package test.src.sqlConnect;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import java.util.Vector;

public class Query {
    String hostIdAddress;       //连接的主机数据库ip地址
    String databaseName = "";        //数据库名
    String SQL;                //SQL语句
    String[] columnName;        //全部字段（列）名
    String[][] record;          //查询到的记录
    String URI;

    public Query(String hostIdAddress) {
        this.hostIdAddress = hostIdAddress;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");//加载JDBC-MySQL驱动
        } catch (Exception e) {
        }
    }

    public void setDatabaseName(String s) {
        databaseName = s.trim();
    }

    public void setSQL(String SQL) {
        this.SQL = SQL.trim();
    }

    public String[] getColumnName() {
        if (columnName == null) {
            System.out.println("先查询记录");
            return null;
        }
        return columnName;
    }

    public String[][] getRecord() {
        startQuery();
        return record;
    }

    public Vector<Vector<String>> getVectorRecord() {
        startQuery();
        Vector<Vector<String>> result = new Vector<>();
        for (String[] row : record) {
            Vector<String> rowItems = new Vector<>(Arrays.asList(row));
            result.add(rowItems);
        }
        return result;
    }

    private void startQuery() {
        Connection con;
        Statement sql;
        ResultSet rs;
        String uri =
                "jdbc:mysql://" + hostIdAddress + ":3306/" +
                        databaseName + "?useSSL=true&characterEncoding=utf-8";
        try {
            con = DriverManager.getConnection(uri, "root", "123456");
            sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs = sql.executeQuery(SQL);
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//字段数目
            columnName = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                columnName[i - 1] = metaData.getColumnName(i);
            }
            rs.last();
            int recordAmount = rs.getRow();  //结果集中的记录数目
            record = new String[recordAmount][columnCount];
            int i = 0;
            rs.beforeFirst();
            while (rs.next()) {
                for (int j = 1; j <= columnCount; j++) {
                    record[i][j - 1] = rs.getString(j); //第i条记录，放入二维数组的第i行
                }
                i++;
            }
            con.close();
        } catch (SQLException e) {
            System.out.println("请输入正确的表名" + e);
        }
    }

    public void toExcel(String path) {
        String[][] record = getRecord();
        SXSSFWorkbook wb = new SXSSFWorkbook();
        SXSSFSheet sheet = wb.createSheet();

        int row_length = record.length;
        int columns_length = record[0].length;

        for (int i = 0; i < row_length; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < columns_length; j++) {
                row.createCell(j).setCellValue(record[i][j]);
                System.out.println(record[i][j] + " ");
            }
            System.out.println();
        }

        try {
            wb.write(new FileOutputStream(path));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public String getURI() {
        this.URI = "jdbc:mysql://" + hostIdAddress + ":3306/" +
                databaseName + "?useSSL=true&characterEncoding=utf-8";
        return URI;
    }

    public static int checkCodeBySql(String hostIdAddress, String userName, String password) {
        Query query = new Query(hostIdAddress);
        query.setDatabaseName("sams");
        query.setSQL("select * from user where userName='" + userName + "'");
        String[][] result = query.getRecord();
        String truePassword;
        if (result.length == 0) return 0; // 表示账号不存在
        else truePassword = result[0][1];
        if (password.equals(truePassword)) return 1; //表示账号密码匹配
        else return -1; //表示密码错误
    }

    public static boolean checkUserNameExist(String hostIdAddress, String newUserName) {
        Query query = new Query(hostIdAddress);
        query.setDatabaseName("sams");
        query.setSQL("select * from user where userName='" + newUserName + "'");
        if (query.getRecord().length == 0) return false;
        else return true;
    }
}